﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Xml;
using System.Xml.Schema;

namespace FilesMerger2._0
{
    class ExcelGenerator
    {
        const String namespaceUri = "urn:schemas-microsoft-com:office:spreadsheet";
        private const String namespaceSecond = "";
        static XmlNamespaceManager nsManager = new XmlNamespaceManager(new NameTable());
        static ExcelGenerator()
        {
            nsManager.AddNamespace("ss", namespaceUri);
        }

        public static XmlNode CreateTableElement(XmlDocument document, String encoding, String sheetName)
        {
            if (GetTableElement(document, sheetName) == null)
            {
                var worksheet = GetWorkSheetElement(document, sheetName) ?? AddWorkSheet(document, sheetName, encoding);
                return worksheet.AppendChild(document.CreateElement("ss", "Table", namespaceUri));
            }
            else
            {
                return GetTableElement(document, sheetName);
            }
        }

        public static XmlNode GetWorkBookElement(XmlDocument document)
        {
            return document.SelectSingleNode("ss:Workbook", nsManager);
        }

        public static XmlNode AddWorkBook(XmlDocument document, String encoding)
        {
            document.AppendChild(document.CreateXmlDeclaration("1.0", encoding, "yes"));
            XmlNode root = document.AppendChild(document.CreateElement("ss", "Workbook", namespaceUri));
            document.DocumentElement.SetAttribute("xmlns:ss", namespaceUri);
            document.DocumentElement.SetAttribute("xmlns", namespaceUri);
            return root;
        }

        public static XmlNode AddWorkSheet(XmlDocument document, String sheetName, String encoding)
        {
            var root = GetWorkBookElement(document) ?? AddWorkBook(document, encoding);
            XmlNode worksheet = root.AppendChild(document.CreateElement("ss", "Worksheet", namespaceUri));
            
            XmlAttribute worksheetAttr = document.CreateAttribute("ss", "Name", namespaceUri);
            worksheetAttr.Value = sheetName;
            worksheet.Attributes.Append(worksheetAttr);
            return worksheet;
        }

        public static XmlNode GetWorkSheetElement(XmlDocument document, String sheetName)
        {
            return document.SelectSingleNode("ss:Workbook/ss:Worksheet[@ss:Name='" + sheetName + "']", nsManager);
        }

        public static XmlNode GetTableElement(XmlDocument document, String sheetName)
        {
            return document.SelectSingleNode("ss:Workbook/ss:Worksheet[@ss:Name='" + sheetName + "']/ss:Table", nsManager);
        }

        public static XmlNode GetStylesElement(XmlDocument document)
        {
            return document.SelectSingleNode("ss:Workbook/ss:Styles", nsManager);
        }

        public static XmlNode AddStylesElement(XmlDocument document, String encoding)
        {
            var workBook = GetWorkBookElement(document) ?? AddWorkBook(document, encoding);
            if (workBook.SelectSingleNode("ss:Worksheet", nsManager) != null)
                return workBook.InsertBefore(document.CreateElement("ss", "Styles", namespaceUri), workBook.SelectSingleNode("ss:Worksheet", nsManager));
            else
                return workBook.AppendChild(document.CreateElement("ss","Styles", namespaceUri));
        }

        public static XmlNode GetStyleElement(XmlDocument document, String styleId)
        {
            return document.SelectSingleNode("ss:Workbook/ss:Styles/ss:Style[@ss:ID='" + styleId + "']", nsManager);
        }

        public static XmlNode AddStyleElement(XmlDocument document, String styleId, String encoding, Color? backgroundColor = null, String pattern = null)
        {
            var styles = GetStylesElement(document) ?? AddStylesElement(document, encoding);
            var style = styles.AppendChild(document.CreateElement("ss","Style", namespaceUri));
            style.Attributes.Append(document.CreateAttribute("ss", "ID", namespaceUri)).Value = styleId;
            var interior = style.AppendChild(document.CreateElement("ss","Interior", namespaceUri));
            if (backgroundColor != null && backgroundColor.HasValue)
                interior.Attributes.Append(document.CreateAttribute("ss", "Color", namespaceUri)).Value =
                    backgroundColor.Value.ToHexString();
            //if (pattern != null && !String.IsNullOrEmpty(pattern))
                interior.Attributes.Append(document.CreateAttribute("ss", "Pattern", namespaceUri)).Value =
                        pattern ?? "None";
            return style;
        }

        public static void AddColumn(XmlDocument document, String sheetName)
        {

            XmlAttribute widthAttr = document.CreateAttribute("ss", "Width", namespaceUri);
            widthAttr.Value = "80";
            var tableElement = GetTableElement(document, sheetName);
            var columnElement = document.CreateElement("ss", "Column", namespaceUri);
            if (document.SelectSingleNode("ss:Workbook/ss:Worksheet[@ss:Name='" + sheetName + "']/ss:Table/ss:Column", nsManager) == null)
                tableElement.AppendChild(columnElement).Attributes.Append(widthAttr);
            else
                tableElement.InsertAfter(columnElement, document.SelectSingleNode("ss:Workbook/ss:Worksheet[@ss:Name='" + sheetName + "']/ss:Table/ss:Column",
                                                                   nsManager)).Attributes.Append(widthAttr);
            //if (document.SelectSingleNode("ss:Workbook/ss:Worksheet/ss:Table/ss:Column", nsManager) == null)
            //    tableElement.AppendChild(columnElement);
            //else
            //    tableElement.InsertAfter(columnElement, document.SelectSingleNode("ss:Workbook/ss:Worksheet/ss:Table/ss:Column",
            //                                                       nsManager));
        }

        public static Int32 GetColumnsCount(XmlDocument document, String sheetName)
        {
            var xmlNodeList = document.SelectNodes("ss:Workbook/ss:Worksheet[@ss:Name='" + sheetName + "']/ss:Table/ss:Column", nsManager);
            if (xmlNodeList != null)
                return xmlNodeList.Count;
            else
                return 0;
        }

        public static void AppendRow(XmlDocument document, String encoding, String sheetName, RowFormat rowFormat = null, params object[] items)
        {
            var tableElement = GetTableElement(document, sheetName);
            if (tableElement == null)
            {
                CreateTableElement(document, encoding, sheetName);
                tableElement = GetTableElement(document, sheetName);
            }
            if (GetColumnsCount(document, sheetName) < items.Length)
            {
                for (Int32 i = 0; i < items.Length - GetColumnsCount(document, sheetName); i++)
                {
                    AddColumn(document, sheetName);
                }
            }

            XmlNode rowNode = tableElement.AppendChild(document.CreateElement("ss", "Row", namespaceUri));
            if (rowFormat != null)
            {
                if (GetStyleElement(document, rowFormat.GetHashCode().ToString()) == null)
                    AddStyleElement(document, rowFormat.GetHashCode().ToString(), encoding,
                                    rowFormat.BackgroundColor, rowFormat.Pattern);
                rowNode.Attributes.Append(document.CreateAttribute("ss", "StyleID", namespaceUri)).Value =
                    rowFormat.GetHashCode().ToString();

            }
            Int32 colindex = 0;
            foreach (var col in items)
            {
                var cell = rowNode.AppendChild(document.CreateElement("ss", "Cell", namespaceUri));
                var data = cell.AppendChild(document.CreateElement("ss", "Data", namespaceUri));
                var typeAttr = document.CreateAttribute("ss", "Type", namespaceUri);
                typeAttr.Value = "String";
                data.Attributes.Append(typeAttr);
                data.InnerText = col.ToString();


                colindex++;
            }
        }
    }
}
